﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using WTAppletProjectHTTPS.Models;

namespace WTAppletProjectHTTPS.Controllers
{
    /// <summary>
    /// 查询全部数据并分页
    /// </summary>
    public class TableListQueryController : ApiController
    {
        private static readonly Logger Logger = NLog.LogManager.GetCurrentClassLogger();
        public class TableList
        {
            public int PageNumber { get; set; } //第几页
            public int PageSize { get; set; }//一页的条数

            public string DateRange { get; set; } //选择的日期范围

            public string OpeationType { get; set; }//表单类型
            public string StartDate { get; set; }//开始日期
            public string EndDate { get; set; }//结束日期

            public string FormType { get; set; }//表单类型
        }

        public class ResultClass
        {
            public int HeadID { get; set; } //ID

            public string HeadDate { get; set; }//创建日期

            public string HeadCreator { get; set; }//创建时间

            public string HeadFormName { get; set; }//填写项目名

            public string HeadForm { get; set; }//填写项目的别名

            public string ImagesPath { get; set; }//上传的文件路径

            public int TotalCount { get; set; }//总的条数

            public int RowNum { get; set; }//行编号

            public DateTime? HeadDateTime { get; set; }
        }
        // POST api/<controller>
        public async Task<HttpResponseMessage> Post([FromBody] TableList TL)
        {
            ReturnObjectQuery RO = new ReturnObjectQuery();
            if (TL == null || TL.PageNumber <= 0 || TL.PageSize <= 0)
            {
                Logger.Warn("参数无效");
                RO.Status = HttpStatusCode.BadRequest;
                RO.Message = "参数无效";
                RO.Data = null;
                Logger.Error("参数无效");
                return Request.CreateResponse(HttpStatusCode.BadRequest, RO);
            }
            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
                {
                    await connection.OpenAsync();

                    var whereClause = "WHERE 1=1";
                    if (TL.DateRange == "今日")
                    {
                        whereClause += $" AND HeadDate between '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 00:00:00' and '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if(TL.DateRange == "昨日")
                    {
                        whereClause += $" AND HeadDate between '{DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd")} 00:00:00' and '{DateTime.Now.Date.AddDays(-1).ToString("yyyy-MM-dd")} 23:59:59'";
                        
                    }
                    else if (TL.DateRange == "本周")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算本周的开始日期（周一）
                        DateTime startOfWeek = today.AddDays(-(int)today.DayOfWeek + (int)DayOfWeek.Monday);

                        // 计算本周的结束日期（周日）
                        DateTime endOfWeek = startOfWeek.AddDays(6);
                        whereClause += $" AND HeadDate between '{startOfWeek.ToString("yyyy-MM-dd")} 00:00:00' and '{endOfWeek.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if (TL.DateRange == "上周")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算本周的开始日期（周一）
                        DateTime startOfWeek = today.AddDays(-(int)today.DayOfWeek + (int)DayOfWeek.Monday);

                        // 计算上周的开始日期（上周一）
                        DateTime startOfLastWeek = startOfWeek.AddDays(-7);

                        // 计算上周的结束日期（上周日）
                        DateTime endOfLastWeek = startOfLastWeek.AddDays(6);
                        whereClause += $" AND HeadDate between '{startOfLastWeek.ToString("yyyy-MM-dd")} 00:00:00' and '{endOfLastWeek.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if (TL.DateRange == "本月")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算本月的第一天
                        DateTime firstDayOfMonth = new DateTime(today.Year, today.Month, 1);

                        // 计算本月的最后一天
                        DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
                        whereClause += $" AND HeadDate between '{firstDayOfMonth.ToString("yyyy-MM-dd")} 00:00:00' and '{lastDayOfMonth.ToString("yyyy-MM-dd")} 23:59:59'";
                        
                    }
                    else if (TL.DateRange == "上月")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算上个月的第一天
                        DateTime firstDayOfLastMonth = new DateTime(today.Year, today.Month, 1).AddMonths(-1);

                        // 计算上个月的最后一天
                        DateTime lastDayOfLastMonth = new DateTime(today.Year, today.Month, 1).AddDays(-1);
                        whereClause += $" AND HeadDate between '{firstDayOfLastMonth.ToString("yyyy-MM-dd")} 00:00:00' and '{lastDayOfLastMonth.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if(TL.DateRange == "今年")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算今年的第一天
                        DateTime firstDayOfYear = new DateTime(today.Year, 1, 1);

                        // 计算今年的最后一天
                        DateTime lastDayOfYear = new DateTime(today.Year, 12, 31);
                        whereClause += $" AND HeadDate between '{firstDayOfYear.ToString("yyyy-MM-dd")} 00:00:00' and '{lastDayOfYear.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if (TL.DateRange == "去年")
                    {
                        // 获取当前日期
                        DateTime today = DateTime.Today;

                        // 计算去年的第一天
                        DateTime firstDayOfLastYear = new DateTime(today.Year - 1, 1, 1);

                        // 计算去年的最后一天
                        DateTime lastDayOfLastYear = new DateTime(today.Year - 1, 12, 31);
                        whereClause += $" AND HeadDate between '{firstDayOfLastYear.ToString("yyyy-MM-dd")} 00:00:00' and '{lastDayOfLastYear.ToString("yyyy-MM-dd")} 23:59:59'";
                        
                    }
                    else if(TL.DateRange == "近7天")
                    {
                        whereClause += $" AND HeadDate between '{DateTime.Now.Date.AddDays(-6).ToString("yyyy-MM-dd")} 00:00:00' and '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 23:59:59'";
                       
                    }
                    else if (TL.DateRange == "近30天")
                    {
                        whereClause += $" AND HeadDate between '{DateTime.Now.Date.AddDays(-29).ToString("yyyy-MM-dd")} 00:00:00' and '{DateTime.Now.Date.ToString("yyyy-MM-dd")} 23:59:59'";
                        
                    }
                    else if (TL.DateRange == "自定义")
                    {
                        whereClause += $" AND HeadDate between '{TL.StartDate} 00:00:00' and '{TL.EndDate} 23:59:59'";
                    }
                    else if(TL.DateRange == "全部")
                    {
                        whereClause += "";
                        
                    }

                    switch (TL.OpeationType)
                    {
                        case "D574 成品关联模组生产日期":
                            whereClause += " and HeadFormName = '成品关联模组生产日期'";
                            break;
                        case "D553 模组测试站换型物料自检确认":
                            whereClause += " and HeadFormName = '测试站换型物料确认'";
                            break;
                        case "D554 扣边框站换型物料自检确认(手工)":
                            whereClause += " and HeadFormName = '扣边框站换型物料确认'";
                            break;
                        case "D555 镭雕站换型自检确认":
                            whereClause += " and HeadFormName = '镭雕站换型自检确认'";
                            break;
                        case "D556 包装工位信息确认":
                            whereClause += " and HeadFormName = '包装站换型信息确认'";
                            break;
                        case "D560 例行检验(生产全检记录)":
                            whereClause += " and HeadFormName = 'Cola 生产全检记录'";
                            break;
                        case "D558 Cola 首件/巡检记录":
                            whereClause += " and HeadFormName = 'Cola首件/巡检记录'";
                            break;
                        case "D559 Cola 终检检验记录":
                            whereClause += " and HeadFormName = 'Cola终检检验记录'";
                            break;
                        case "D587 Cola产品功能测试":
                            whereClause += " and HeadFormName = 'Cola 产品功能测试'";
                            break;
                        case "D562 包装后全检记录":
                            whereClause += " and HeadFormName = 'Cola产线包装后全检'";
                            break;
                        case "D595 QC抽检成品边框":
                            whereClause += " and HeadFormName = 'QC抽检成品边框'";
                            break;
                        case "D594 A1/A2员工边框上料确认(自动)":
                            whereClause += " and HeadFormName = 'A1/A2员工边框上料确认'";
                            break;
                        case "全部":
                            whereClause += "";
                            break;
                        default:
                            break;
                    }
                    
                    var query = $@"select T.HeadID,T.HeadDate,T.HeadCreator,T.HeadFormName,T.HeadForm,T.ImagesPath,T.TotalCount,T.RowNum AS RN from (
select *,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY HeadDate desc) AS RowNum from (
select * from (select PADID HeadID,PADCreateTime HeadDate,PADCreateCreator HeadCreator,'成品关联模组生产日期' as HeadFormName,'ProductAssociationDate' as HeadForm,PADModuleProductionDatePhotoScan ImagesPath  
from ProductAssociationDate where PADCreateTime IS NOT NULL) PAD
union 
select * from (select TSCMCID HeadID,TSCMCCreateTime HeadDate,TSCMCCreateCreator HeadCreator,'测试站换型物料确认' as HeadFormName,'TestStationChangeMaterialConfirmation' as HeadForm,TSCMCNonconformanceImage ImagesPath 
from TestStationChangeMaterialConfirmation where TSCMCCreateTime IS NOT NULL) TSCMC
union
select * from (select FSCMCID HeadID,FSCMCCreateTime HeadDate,FSCMCCreateCreator HeadCreator,'扣边框站换型物料确认' as HeadFormName,'FrameStationChangeMaterialConfirmation' as HeadForm,FSCMCNonconformanceImage ImagesPath 
from FrameStationChangeMaterialConfirmation where FSCMCCreateTime IS NOT NULL) FSCMC
union 
select * from (select LMSSCID HeadID,LMSSCCreateTime HeadDate,LMSSCCreateCreator HeadCreator,'镭雕站换型自检确认' as HeadFormName,'LaserMarkingStationSelfInspectionConfirmation' as HeadForm,LMSSCNonconformanceImage ImagesPath 
from LaserMarkingStationSelfInspectionConfirmation where LMSSCCreateTime IS NOT NULL ) LMSSC
union
select * from (select PSCICID HeadID,PSCICCreateTime HeadDate,PSCICCreateCreator HeadCreator,'包装站换型信息确认' as HeadFormName,'PackagingStationChangeInformationConfirmation' as HeadForm,CONCAT(PSCICPlasticBagFilmPhoto, PSCICThermalTransferLabelPhoto, PSCICColorBoxPhoto,PSCICColorBoxLabelPhoto, PSCICOuterBoxLabelPhoto) AS ImagesPath 
from PackagingStationChangeInformationConfirmation where PSCICCreateTime IS NOT NULL) PSCIC
union
select * from (select CPFIRID HeadID,CPFIRCreateTime HeadDate,CPFIRCreateCreator HeadCreator,'Cola 生产全检记录' as HeadFormName,'ColaProductionFullInspectionRecord' as HeadForm,'' as ImagesPath  
from ColaProductionFullInspectionRecord where CPFIRCreateTime IS NOT NULL ) CPFIR
union 
select * from (select CFPID HeadID,CFPCreateTime HeadDate,CFPCreateCreator HeadCreator,'Cola首件/巡检记录' as HeadFormName,'ColaFirstPiecePatrolInspectionRecord' as HeadForm,CONCAT(CFPNonconformanceImage1,CFPNonconformanceImage2,CFPNonconformanceImage3,CFPNonconformanceImage4) as ImagesPath 
from ColaFirstPiecePatrolInspectionRecord where CFPCreateTime IS NOT NULL) CFP 
union 
select * from (select CFIRID HeadID,CFIRCreateTime HeadDate,CFIRCreateCreator HeadCreator,'Cola终检检验记录' as HeadFormName,'ColaFinalInspectionRecord' as HeadForm,CONCAT(CFIRNonconformanceImage1,CFIRNonconformanceImage2,CFIRNonconformanceImage3) ImagesPath 
from ColaFinalInspectionRecord where CFIRCreateTime IS NOT NULL) CFIR 
union 
select * from (select CPFTID HeadID,CPFTCreateTime HeadDate,CPFTCreateCreator HeadCreator,'Cola 产品功能测试' as HeadFormName,'ColaProductFunctionTest' as HeadForm,'' as ImagesPath 
from ColaProductFunctionTest where CPFTCreateTime IS NOT NULL) CPFT 
union 
select * from (select CPFIID HeadID,CPFICreateTime HeadDate,CPFICreateCreator HeadCreator,'Cola产线包装后全检' as HeadFormName,'ColaPackingFinalInspection' as HeadForm,'' as ImagesPath 
from ColaPackingFinalInspection where CPFICreateTime IS NOT NULL) CPFT 
union 
select * from (select QCSID HeadID,QCSCreateTime HeadDate,QCSCreateCreator HeadCreator,'QC抽检成品边框' as HeadFormName,'QCSamplingFinishedFrame' as HeadForm,QCSPhotoUpload as ImagesPath 
from QCSamplingFinishedFrame where QCSCreateTime IS NOT NULL and QCSFormType = 'TS Cola生产自检') CPFT 
union 
select * from (select FFCID HeadID,FFCCreateTime HeadDate,FFCCreateCreator HeadCreator,'A1/A2员工边框上料确认' as HeadFormName,'A1A2EmployeeFrameFeedingConfirmation' as HeadForm,FFCPhotoUpload as ImagesPath 
from A1A2EmployeeFrameFeedingConfirmation where FFCCreateTime IS NOT NULL and FFCFormType = 'TS Cola生产自检') CPFT ) Tatol {whereClause}) T WHERE RowNum BETWEEN @StartRow AND @EndRow  ORDER BY RowNum";
                    var query1 = $@"select T.HeadID,T.HeadDate,T.HeadCreator,T.HeadFormName,T.HeadForm,T.ImagesPath,T.TotalCount,T.RowNum AS RN from (
select *,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY HeadDate desc) AS RowNum from (
select * from (select QCSID HeadID,QCSCreateTime HeadDate,QCSCreateCreator HeadCreator,'QC抽检成品边框' as HeadFormName,'QCSamplingFinishedFrame' as HeadForm,QCSPhotoUpload as ImagesPath 
from QCSamplingFinishedFrame where QCSCreateTime IS NOT NULL and QCSFormType = 'TS A1线 面盖物料确认') CPFT 
union 
select * from (select FFCID HeadID,FFCCreateTime HeadDate,FFCCreateCreator HeadCreator,'A1/A2员工边框上料确认' as HeadFormName,'A1A2EmployeeFrameFeedingConfirmation' as HeadForm,FFCPhotoUpload as ImagesPath 
from A1A2EmployeeFrameFeedingConfirmation where FFCCreateTime IS NOT NULL and FFCFormType = 'TS A1线 面盖物料确认') CPFT ) Tatol {whereClause}) T WHERE RowNum BETWEEN @StartRow AND @EndRow  ORDER BY RowNum";
                    var selectquery = "";
                    if (TL.FormType == "F1")
                    {
                        selectquery = query;
                    }
                    else if(TL.FormType == "F2")
                    {
                        selectquery = query1;
                    }
                    int startRow = (TL.PageNumber - 1) * TL.PageSize + 1;
                    int endRow = TL.PageNumber * TL.PageSize;
                    var parameters = new List<SqlParameter>
                    {
                        new SqlParameter("@StartRow", SqlDbType.Int) { Value = startRow },
                        new SqlParameter("@EndRow", SqlDbType.Int) { Value = endRow }
                    };

                    using (var command = new SqlCommand(selectquery, connection))
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (!reader.HasRows)
                            {
                                Logger.Warn("该页没有记录");
                                RO.Status = HttpStatusCode.OK;
                                RO.Message = "该页没有记录";
                                RO.Data = null;
                                return Request.CreateResponse(HttpStatusCode.OK, RO);
                            }

                            var products = new List<ResultClass>();
                            int totalCount = 0;
                            while (await reader.ReadAsync())
                            {
                                if (totalCount == 0)
                                {
                                    totalCount = reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                string HDString = "";
                                if ((DateTime.Now.Date.Day - reader.GetDateTime(reader.GetOrdinal("HeadDate")).Date.Day) == 0)
                                {
                                    HDString = "今天 " + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Hour + ":" + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Minute;
                                }
                                else if ((DateTime.Now.Date.Day - reader.GetDateTime(reader.GetOrdinal("HeadDate")).Date.Day) == 1)
                                {
                                    HDString = "昨天 " + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Hour + ":" + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Minute;
                                }
                                else if ((DateTime.Now.Date.Day - reader.GetDateTime(reader.GetOrdinal("HeadDate")).Date.Day) == 2)
                                {
                                    HDString = "前天 " + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Hour + ":" + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Minute;
                                }
                                else
                                {
                                    HDString = reader.GetDateTime(reader.GetOrdinal("HeadDate")).Date.ToString("yyyy-MM-dd") + " " + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Hour + ":" + reader.GetDateTime(reader.GetOrdinal("HeadDate")).Minute;
                                }
                                
                                products.Add(new ResultClass
                                {
                                    HeadID = reader.GetInt32(reader.GetOrdinal("HeadID")),
                                    HeadDate = HDString,
                                    HeadDateTime = reader.GetDateTime(reader.GetOrdinal("HeadDate")),
                                    HeadCreator = reader.GetString(reader.GetOrdinal("HeadCreator")),
                                    HeadFormName = reader.GetString(reader.GetOrdinal("HeadFormName")),
                                    HeadForm = reader.GetString(reader.GetOrdinal("HeadForm")),
                                    ImagesPath = reader.GetString(reader.GetOrdinal("ImagesPath")),
                                    TotalCount = reader.GetInt32(reader.GetOrdinal("TotalCount"))
                                    
                                });
                            }

                            Logger.Warn("未找到记录");
                            RO.Status = HttpStatusCode.OK;
                            RO.Message = "OK";
                            RO.Data = products;
                            RO.TotalCount = totalCount;
                            RO.PageNumber = TL.PageNumber;
                            RO.PageSize = TL.PageSize;
                            return Request.CreateResponse(HttpStatusCode.OK, RO);
                        }
                    }
                };

            }
            catch (Exception ex)
            {
                Logger.Error("查询失败: " + ex.Message);
                RO.Status = HttpStatusCode.InternalServerError;
                RO.Message = "Error: " + ex.Message;
                RO.Data = null;
                return Request.CreateResponse(HttpStatusCode.InternalServerError, RO);
            }
        }
    }
}